Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Shared Session

In the multithreaded server configuration, the session information is also stored in the shared pool. This information includes the private SQL areas as well as sort areas. It is important to make sure that you do not run out of space in the shared pool for this information.

To determine whether you should increase space for these shared sessions, you can extract the sum of memory allocated for all sessions and the maximum amount of memory allocated for sessions from the dynamic performance table V$SESSTAT. The information in this table is split into the memory used by the UGA (User Global Area) and that used by the PGA (Program Global Area). To retrieve this information, use a query such as the one in Listing 9.1.

Listing 9.1 Retrieving Information from V$SESSTAT

select sid, value "Session UGA Memory"
from v$sesstat,v$statname
WHERE name = 'session uga memory' AND
v$sesstat.statistic# = v$statname.statistic#;

select SUM(value) "Sum of Session UGA Memory"
from v$sesstat,v$statname
WHERE name = 'session uga memory' AND
v$sesstat.statistic# = v$statname.statistic#;

select sid, value "Session PGA Memory"
from v$sesstat,v$statname
WHERE name = 'session pga memory' AND
v$sesstat.statistic# = v$statname.statistic#;

select SUM(value) "Sum of Session PGA Memory"
from v$sesstat,v$statname
WHERE name = 'session pga memory' AND
v$sesstat.statistic# = v$statname.statistic#;

select sid, value "Session UGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session uga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

select SUM(value) "Sum of Session UGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session uga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

select sid, value "Session PGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session pga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

select SUM(value) "Sum of Session PGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session pga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

The result of this query looks something like Listing 9.2.

Listing 9.2 The Results of the Query in Listing 9.1

SID Session UGA Memory
---------- ------------------
         1               7048
        54              43928
         5              14104
         4               7048
         3               7048
         2               8956

6 rows selected.

Sum of Session UGA Memory
------------------------
                    83896

       SID Session PGA Memory
---------- ------------------
         1              34032
        54              76592
         5              51056
         2              42544
         4              42544
         3              42544

6 rows selected.

Sum of Session PGA Memory
------------------------
                   289312

       SID Session UGA Memory Max
---------- ----------------------
         1                   7048
         3                   7048
         5                  18288
        54                  48168
         4                   7048
         2                   8956

6 rows selected.

Sum of Session UGA Memory Max
----------------------------
                        96556

       SID Session PGA Memory Max
---------- ----------------------
         1                  34032
         2                  42544
         4                  42544
        54                  76592
         5                  51056
         3                  42544

6 rows selected.

Sum of Session PGA Memory Max
-----------------------------
                       289312

The SID represents the session ID, which shows you the value for each server session.

Tuning the Buffer Cache

Probably the most important Oracle cache in the system is the buffer cache. The buffer cache makes up the majority of the Oracle SGA and is used for every query and update in the system.

Each time a data block is read, it is copied into the buffer cache. Each time a modification is made, it is done in the buffer cache. Remember that each server process accesses the buffer cache directly.

In a read operation, the server process first checks to see whether the requested data is already in the SGA. If it is, the data is accessed directly from the SGA. If the data is not already in the SGA, the server process copies the data from the data file into the SGA where it will be accessed.

In an update operation, the server process modifies the data block buffer(s) in the SGA only. It is up to the DBWR to write these dirty buffers out to disk. With the exception of the CKPT process, only the DBWR writes to the data files.

Because the buffer cache is accessed so frequently, it is important that the buffer cache has sufficient size to get a good cache-hit rate. The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The important columns to view in this table are as follows:

  PHYSICAL READS: This is the total number of requests that result in a disk access; this is a cache miss.
  DB BLOCK GETS and CONSISTENT GETS: The sum of the two values DB BLOCK GETS and CONSISTENT GETS represents the total number of requests for data.

To see how well the block buffer cache is doing, use this query:

SQL> SELECT name, value
  2  FROM v$sysstat
  3  WHERE name IN ('db block gets', 'consistent gets', 'physical reads');

NAME                                                                 VALUE
----------------------------------------------------------------  --------
db block gets                                                          155
consistent gets                                                       5293
physical reads                                                         334

To calculate the cache hit ratio, use this formula:

Cache Hit Ratio = 1 - ( PHYSICAL READS / ( DB BLOCK GETS + CONSISTENT GETS))
Cache Hit Ratio = 1 - (334 / ( 155 + 5293) ) = 1 -(334 / 5448) = 1 - 0.0613 = 0.938

This example shows a cache hit rate of 93.8 percent.

If the cache-hit rate is lower than 70 or 80 percent, you may need to increase the database buffer cache to improve performance. The buffer cache can be increased by tuning the Oracle initialization parameter DB_BLOCK_BUFFERS.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.